Namespace persistent
    Public Class smeTariffUpdate

        Public Shared Function check_contract(ByVal custcode As String, ByVal tmcode As String) As Integer
            Dim strSQL As String
            strSQL = " select count(*)  from customer_all cu, contract_all co, curr_co_status cur "
            strSQL = strSQL + " where cu.custcode = '" & custcode & "' "
            strSQL = strSQL + " and co.customer_id=cu.customer_id "
            strSQL = strSQL + " and cur.co_id=co.co_id "
            strSQL = strSQL + " and co.tmcode not in(" & tmcode & ",466,483) "
            strSQL = strSQL + " and cur.ch_status ='a' "

            Return myDBConn.myORATable(strSQL).Rows(0)(0)
        End Function

        Public Shared Function Update_contr_services(ByVal coID As String) As Long
            Dim strSQL As String

            strSQL = " update contr_services cs1 set cs_date_billed=to_date('31.12.2015','dd.mm.yyyy') where co_id=" + coID + " and cs_date_billed is null"
            strSQL = strSQL + " and cs_seqno = (select max(cs_seqno) from contr_services where co_id=cs1.co_id)"

            Return myDBConn.ORAExecute(strSQL)

        End Function

        Public Shared Function check_contract_activatedDays(ByVal custcode As String, ByVal tmcode As String) As Integer
            Dim strSQL As String
            strSQL = " select count(*)  from customer_all cu, contract_all co, curr_co_status cur "
            strSQL = strSQL + " where cu.custcode = '" & custcode & "' "
            strSQL = strSQL + " and co.customer_id=cu.customer_id "
            strSQL = strSQL + " and cur.co_id=co.co_id "
            strSQL = strSQL + " and co.tmcode not in(466,483) "
            strSQL = strSQL + " and cur.ch_status ='a' "
            strSQL = strSQL + " and to_number((sysdate-co_activated))<(select days from rateplan_days where tmcode=co.tmcode)"

            Return myDBConn.myORATable(strSQL).Rows(0)(0)
        End Function

        Public Shared Function check_contract_mobilData(ByVal custcode As String, ByVal tmcode As String) As Integer
            Dim strSQL As String
            strSQL = " select count(*)  from customer_all cu, contract_all co, curr_co_status cur "
            strSQL = strSQL + " where cu.custcode = '" & custcode & "' "
            strSQL = strSQL + " and co.customer_id=cu.customer_id "
            strSQL = strSQL + " and cur.co_id=co.co_id "
            strSQL = strSQL + " and co.tmcode not in(" & tmcode & ",466,483) "
            strSQL = strSQL + " and cur.ch_status ='a' "
            strSQL = strSQL + " and co_activated > add_months(sysdate,-3)"


            Return myDBConn.myORATable(strSQL).Rows(0)(0)
        End Function

        Public Shared Function check_mobilData(ByVal custcode As String) As Integer
            Dim strSQL As String
            strSQL = " select count(*)  from customer_all cu, contract_all co, curr_co_status cur "
            strSQL = strSQL + " where cu.custcode = '" & custcode & "' "
            strSQL = strSQL + " and co.customer_id=cu.customer_id "
            strSQL = strSQL + " and cur.co_id=co.co_id "
            strSQL = strSQL + " and co.tmcode in (483) "
            strSQL = strSQL + " and cur.ch_status ='a' "

            Return myDBConn.myORATable(strSQL).Rows(0)(0)
        End Function

        Public Shared Function get_contract(ByVal custcode As String, ByVal tmcode As String) As DataTable
            Dim strSQL As String
            strSQL = " select co.co_id, co.co_itemized_bill, co.CO_OPERATOR_DIR  from customer_all cu, contract_all co, curr_co_status cur "
            strSQL = strSQL + " where cu.custcode = '" & custcode & "' "
            strSQL = strSQL + " and co.customer_id=cu.customer_id "
            strSQL = strSQL + " and cur.co_id=co.co_id "
            strSQL = strSQL + " and co.tmcode not in(" & tmcode & ",466,483) "
            strSQL = strSQL + " and cur.ch_status ='a' "

            Return myDBConn.myORATable(strSQL)
        End Function

        Public Shared Function GetContracts(ByVal custcode As String) As DataTable
            Dim sql As String
            sql = "select v1.* from ("
            sql += " select v.*, (select ch_status from curr_co_status where co_id=v.co_id ) statu ,"
            sql += " (select des from mputmtab where tmcode=v.tmcode and vscode=(select max(vscode) from mputmtab where tmcode=v.tmcode)) tmcode_desc"
            sql += " from ( "
            sql += " select distinct cslevel,custcode,fn_coid_to_msisdn(co.co_id) ms,co.co_id,co.co_activated,co.tmcode  from customer_all cu, contract_all co "
            sql += " where (cu.custcode like  '" & custcode & ".%' or cu.custcode='" & custcode & "' )  "
            sql += " and cu.customer_id=co.customer_id"
            sql += " and cu.cslevel='40'"
            sql += " ) v  ) v1 where v1.statu in ('a','s') "

            Return myDBConn.myORATable(sql)

        End Function

        Public Shared Function get_prgcode(ByVal tmcode As String) As DataTable
            Dim strSQL As String
            strSQL = " select lpad(prgcode,2,'0') from mpulknxg where tmcode= " & tmcode
            strSQL = strSQL + " and prgcode !=27 and rownum<2 "

            Return myDBConn.myORATable(strSQL)
        End Function
        Public Shared Function get_service(ByVal coid As String, ByVal tmcode As String) As DataTable
            Dim strSQL As String
            strSQL = " select sncode,spcode from contr_services where co_id=" & coid
            strSQL = strSQL + " and sncode not in( "
            strSQL = strSQL + " select sncode from mpulktmb m where tmcode = " & tmcode
            strSQL = strSQL + " and vscode=(select max(vscode) from mpulktmb m1 where m1.tmcode=m.tmcode) "
            strSQL = strSQL + "  ) "
            strSQL = strSQL + " and cs_stat_chng like '%a' "

            Return myDBConn.myORATable(strSQL)
        End Function

        Public Shared Function get_service_diff(ByVal tm1 As String, ByVal tm2 As String) As DataTable
            Dim strSQL As String
            strSQL = " select sncode from mpulktmb m where tmcode=" & tm1
            strSQL = strSQL + " and vscode=(select max(vscode) from mpulktmb m1 where m1.tmcode=m.tmcode) "
            strSQL = strSQL + " and csind='X' "
            strSQL = strSQL + " minus "
            strSQL = strSQL + " select sncode from mpulktmb m where tmcode=" & tm2
            strSQL = strSQL + " and vscode=(select max(vscode) from mpulktmb m1 where m1.tmcode=m.tmcode) "
            strSQL = strSQL + " and csind='X' "

            Return myDBConn.myORATable(strSQL)
        End Function

        Public Shared Function get_valide_prg(ByVal custcode As String) As String
            Dim strSQL As String
            strSQL = " select count(1) from customer_all where custcode='" & custcode & "' and prgcode in ('05','08','09','24','25','26','27','30','31') "
            Return myDBConn.myORATable(strSQL).Rows(0)(0)
        End Function

        Public Shared Function get_tmcode() As DataTable
            Dim strSQL As String
            'strSQL = " select tmcode,des from mputmtab m1 where tmcode in(261,262,263,465,464,573,574,575) "
            strSQL = " select tmcode,des from mputmtab m1 where tmcode in (262,573,574,575, 597, 598, 586, 604) "
            strSQL = strSQL + " and vscode =(select max(vscode) from mputmtab where tmcode =m1.tmcode) "
            Return myDBConn.myORATable(strSQL)
        End Function

        Public Shared Function get_tmcode_history(ByVal coid As String) As DataTable
            Dim strSQL As String
            strSQL = " select distinct tmcode,cs_seqno, "
            strSQL += " (select des from mputmtab m where tmcode=cs.tmcode and vscode=(select max(vscode) from mputmtab where tmcode=m.tmcode and status='P') ) "
            strSQL += " from contr_services cs where co_id =" & coid
            Return myDBConn.myORATable(strSQL)
        End Function

        Public Shared Function Get_User_Count(ByVal tarih_baslangic As String, ByVal tarih_bitis As String) As DataTable
            Dim strSQL As String
            strSQL = " select trunc(tarih) tarih,username,count(*) count from batch_req where job_id='JB2' "
            strSQL += " and trunc(tarih) between '" & tarih_baslangic & "' and '" & tarih_bitis & "' group by trunc(tarih),username"

            Return myDBConn.myORATable(strSQL)
        End Function

        Public Shared Function Get_Proc_Count(ByVal tarih_baslangic As String, ByVal tarih_bitis As String) As DataTable
            Dim strSQL As String
            strSQL = " select trunc(tarih) tarih,count(*) count from batch_req where job_id='JB2' "
            strSQL += " and trunc(tarih) between '" & tarih_baslangic & "' and '" & tarih_bitis & "' group by trunc(tarih)"

            Return myDBConn.myORATable(strSQL)
        End Function

        Public Shared Function Get_Error_Proc(ByVal tarih_baslangic As String, ByVal tarih_bitis As String) As DataTable
            Dim strSQL As String

            strSQL = " select b1.tarih,b1.username,b1.req_id, "
            strSQL += " (select value from batch_req_param where req_id=b1.req_id and parameterno=1) sirket, "
            strSQL += " (select value from batch_req_param where req_id=b1.req_id and parameterno=2) tarife from batch_req b1 where "
            strSQL += " trunc(tarih) between '" & tarih_baslangic & "' and '" & tarih_bitis & "' "
            strSQL += " and b1.status=2 "
            strSQL += " and b1.job_id='JB2' "

            Return myDBConn.myORATable(strSQL)
        End Function

        Public Shared Function Get_Firm_Tmcode(ByVal tarih_baslangic As String, ByVal tarih_bitis As String) As DataTable
            Dim strSQL As String

            strSQL = "  select t3.tarih,t3.sirket,t3.old_tmcode, "
            strSQL += " (select des from mputmtab where tmcode=t3.old_tmcode and vscode=(select max(vscode) from mpulktmb m1 where m1.tmcode=t3.old_tmcode)) old_tmcode_desc, "
            strSQL += " t3.new_tmcode, "
            strSQL += " (select des from mputmtab where tmcode=t3.new_tmcode and vscode=(select max(vscode) from mpulktmb m1 where m1.tmcode=t3.new_tmcode)) new_tmcode_desc, t3.count "
            strSQL += " from ( "
            strSQL += " select t2.tarih,t2.sirket, t2.old_tmcode,t2.tarife new_tmcode,count(*) count  from ( "
            strSQL += " select m1.*,ct.co_id, "
            strSQL += " (select distinct tmcode from RATEPLAN_HIST r where co_id=ct.co_id "
            strSQL += " and seqno = (select max(seqno)-1 from rateplan_hist where co_id=ct.co_id)) old_tmcode "
            strSQL += " from "
            strSQL += " ( "
            strSQL += " select t1.*,ca.customer_id from "
            strSQL += " ( "
            strSQL += " select b1.tarih, "
            strSQL += " (select value from batch_req_param where req_id=b1.req_id and parameterno=1) sirket, "
            strSQL += " (select value from batch_req_param where req_id=b1.req_id and parameterno=2) tarife from batch_req b1 where "
            strSQL += " trunc(tarih) between '" & tarih_baslangic & "' and '" & tarih_bitis & "' "
            strSQL += " and b1.status=1"
            strSQL += " and b1.job_id='JB2' "
            strSQL += " ) t1,customer_all ca "
            strSQL += " where(t1.sirket = ca.custcode) "
            strSQL += " ) m1,contract_all ct where m1.customer_id=ct.customer_id "
            strSQL += " and m1.tarife=ct.tmcode "
            strSQL += " )t2 where t2.old_tmcode is not null  "
            strSQL += " group by t2.tarih,t2.sirket,t2.old_tmcode,t2.tarife  "
            strSQL += " ) t3 "

            Return myDBConn.myORATable(strSQL)
        End Function





    End Class
End Namespace
